﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Business;
using Model;
using System.Web.Security;
using MB.Util;
using System.Data.OleDb;
using System.Data;
using System.Transactions;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace MvcAppWeb.Controllers
{
    public class ShelvesController : BaseDataController
    {
        //
        // GET: /Shelves/


        #region 下架指令
        public ActionResult OffShelves()
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult QueryOffShelves(string SBatchNO, string SGOODSCODE, string SFlag, string CheckTimeS, string CheckTimeE, string CellNo, string SGOODSNAME)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SBatchNO))
            {
                where += " and os.BatchNO like'%" + SBatchNO + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SFlag))
            {
                where += " and os.Flag =" + SFlag;
            }
            if (!string.IsNullOrEmpty(CheckTimeS))
            {
                where += " and os.OFFSHELVESDATE>= to_date('" + CheckTimeS + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(CheckTimeE))
            {
                where += " and os.OFFSHELVESDATE<= to_date('" + CheckTimeE + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(CellNo) && CellNo != "全部")
            {
                where += " and l.CellNo ='" + CellNo + "'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            BShelves nc = new BShelves();
            var result = nc.GetOffShelvesList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public string Saves(string OFFSHELVESID)
        {
            string sjson = string.Empty;
            try
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                string BatchNo = Request["BatchNo"];
                string GoodsCode = Request["txtGoodsCode"];
                string QTY = Request["txtQTY"];
                string OffShelvesDate = Request["OffShelvesDate"];
                string Remark = Request["txtRemark"];
                OffShelves _OffShelves = new OffShelves();
                sjson = CheckOffShelves(OFFSHELVESID, BatchNo, QTY, OffShelvesDate, GoodsCode, Remark, out _OffShelves);

                if (!string.IsNullOrEmpty(sjson))
                    return sjson;

                BShelves nbs = new BShelves();
                if (string.IsNullOrEmpty(OFFSHELVESID))
                {
                    nbs.AddOffShelves(_OffShelves);
                }
                else
                {
                    _OffShelves.OFFSHELVESID = int.Parse(OFFSHELVESID);
                    nbs.EditOffShelvesByID(_OffShelves);
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;
        }

        [HttpPost]
        public string Delete(string ids)
        {
            string sjson = string.Empty;
            try
            {
                BShelves nbl = new BShelves();
                foreach (var id in ids.Split(','))
                {
                    nbl.DeleteOffShelves(int.Parse(id));
                }
                sjson = "{success:true}";
            }
            catch (Exception ex)
            {
                sjson = "{success:false,msg:'" + ex.Message + "'}";
            }
            return sjson;

        }

        [HttpPost]
        public string ImportOffShelves(HttpPostedFileBase filebase)
        {
            string sjson = string.Empty;
            HttpPostedFileBase file = Request.Files["files"];
            DataTable table = new DataTable();
            string importStr = string.Empty;
            sjson = ImportFile(file, out table, out importStr);
            if (!string.IsNullOrEmpty(sjson))
                return sjson;

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope())
            {
                try
                {
                    if (!string.IsNullOrEmpty(importStr))//上传的是TXT
                    {
                        List<string> lstStr = importStr.Split(';').ToList();
                        foreach (string item in lstStr)
                        {
                            if (string.IsNullOrEmpty(item))
                                continue;
                            List<string> lstModelStr = item.Split(',').ToList();
                            if (lstModelStr.Count != 5)
                            {
                                sjson = "{success:false,msg:'TXT文件内容格式不正确！'}";
                                return sjson;
                            }
                            string BatchNo = lstModelStr[0].ToString();
                            string GoodsCode = lstModelStr[1].ToString();
                            string QTY = lstModelStr[2].ToString();
                            string OffShelvesDate = lstModelStr[3].ToString();
                            string Remark = lstModelStr[4].ToString();
                            OffShelves _OffShelves = new OffShelves();
                            sjson = CheckOffShelves(string.Empty, BatchNo, QTY, OffShelvesDate, GoodsCode, Remark, out _OffShelves);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BShelves nbs = new BShelves();
                            nbs.AddOffShelves(_OffShelves);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            string BatchNo = table.Rows[i][0].ToString();
                            string GoodsCode = table.Rows[i][1].ToString();
                            string QTY = table.Rows[i][5].ToString();
                            string OffShelvesDate = table.Rows[i][8].ToString();
                            string Remark = table.Rows[i][13].ToString();
                            OffShelves _OffShelves = new OffShelves();
                            sjson = CheckOffShelves(string.Empty, BatchNo, QTY, OffShelvesDate, GoodsCode, Remark, out _OffShelves);

                            if (!string.IsNullOrEmpty(sjson))
                                return sjson;
                            BShelves nbs = new BShelves();
                            nbs.AddOffShelves(_OffShelves);
                        }
                    }
                    transaction.Complete();
                }
                catch (Exception ex)
                {
                    sjson = "{success:false,msg:'" + ex.Message + "'}";
                    return sjson;
                }
            }
            sjson = "{success:true}";
            System.Threading.Thread.Sleep(2000);
            return sjson;
        }

        public FileResult ExportExcel(int page, int rows)
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int total = 0;
            string SBatchNO = Request["txtSBatchNO"];
            string SGOODSCODE = Request["txtSGOODSCODE"];
            string SFlag = Request["SFlag"];
            string CheckTimeS = Request["CheckTimeS"];
            string CheckTimeE = Request["CheckTimeE"];
            string CellNo = Request["CellNo"];
            string SGOODSNAME = Request["txtSGOODSNAME"];
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(SBatchNO))
            {
                where += " and os.BatchNO like'%" + SBatchNO + "%'";
            }
            if (!string.IsNullOrEmpty(SGOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + SGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(SFlag))
            {
                where += " and os.Flag =" + SFlag;
            }
            if (!string.IsNullOrEmpty(CheckTimeS))
            {
                where += " and os.OFFSHELVESDATE>= to_date('" + CheckTimeS + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(CheckTimeE))
            {
                where += " and os.OFFSHELVESDATE<= to_date('" + CheckTimeE + "','yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrEmpty(CellNo) && CellNo != "全部")
            {
                where += " and l.CellNo ='" + CellNo + "'";
            }
            if (!string.IsNullOrEmpty(SGOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + SGOODSNAME + "%'";
            }
            BShelves nc = new BShelves();
            List<OffShelves> list = nc.GetOffShelvesList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string> { "作业批次号", "商品编码", "商品名称","库位","货位", "数量", "执行数量","状态","指定配送日期", 
                "创建人", "创建时间", "修改人", "修改时间", "备注" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].BATCHNO);
                rowtemp.CreateCell(1).SetCellValue(list[i].GOODSCODE);
                rowtemp.CreateCell(2).SetCellValue(list[i].GOODSNAME);
                rowtemp.CreateCell(3).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(4).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(5).SetCellValue(list[i].QTY.ToString());
                rowtemp.CreateCell(6).SetCellValue(list[i].QTYEXCE.ToString());
                rowtemp.CreateCell(7).SetCellValue(FormatFlag(list[i].FLAG));
                rowtemp.CreateCell(8).SetCellValue(list[i].OFFSHELVESDATE.ToString());
                rowtemp.CreateCell(9).SetCellValue(list[i].CREATER);
                rowtemp.CreateCell(10).SetCellValue(list[i].CREATEDATE.ToString());
                rowtemp.CreateCell(11).SetCellValue(list[i].EDITOR);
                rowtemp.CreateCell(12).SetCellValue(list[i].EDITDATE.ToString());
                rowtemp.CreateCell(13).SetCellValue(list[i].REMARK);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("OffShelves"));
        }
        #endregion

        private string FormatFlag(string flag)
        {
            string str = string.Empty;
            if (flag == "0")
            {
                str = "未处理";
            }
            if (flag == "40")
            {
                str = "在执行";
            }
            if (flag == "100")
            {
                str = "完结";
            }
            return str;
        }

        private string CheckOffShelves(string OFFSHELVESID, string BatchNo, string QTY, string OffShelvesDate, string GoodsCode, string Remark, out OffShelves offShelves)
        {
            string sjson = string.Empty;
            AppUserList model = (AppUserList)Session["UserInfo"];
            offShelves = new OffShelves();
            offShelves.BATCHNO = BatchNo;
            decimal TQTY = 0;
            if (decimal.TryParse(QTY, out TQTY))
                offShelves.QTY = TQTY;
            DateTime dt = DateTime.Now;
            if (DateTime.TryParse(OffShelvesDate, out dt))
                offShelves.OFFSHELVESDATE = dt;
            offShelves.FLAG = "0";
            offShelves.REMARK = Remark;
            offShelves.CREATER = model.USERNAME;
            offShelves.CREATEDATE = DateTime.Now;
            offShelves.EDITOR = model.USERNAME;
            offShelves.EDITDATE = DateTime.Now;

            BLocation nbl = new BLocation();
            Goods checkgoods = nbl.CheckGoods(GoodsCode);
            if (checkgoods != null)
            {
                offShelves.GOODSID = checkgoods.GOODSID;
            }
            else
            {
                sjson = "{success:false,msg:'商品编码不存在！'}";
                return sjson;
            }
            BShelves nbs = new BShelves();
            List<OffShelves> lstOffShelves = nbs.CheckOffShelvesPrimary(offShelves.GOODSID, dt);
            if (lstOffShelves != null)
            {
                if (lstOffShelves.Where(_ => _.BATCHNO == BatchNo && _.OFFSHELVESID.ToString() != OFFSHELVESID).ToList().Count > 0)
                {
                    sjson = "{success:false,msg:'该指令已存在！'}";
                    return sjson;
                }
            }
            return sjson;
        }

    }
}
